//
//  SQLiteManager.swift
//  数据库OC
//
//  Created by yang on 2018/1/7.
//  Copyright © 2018年 YYY. All rights reserved.
//

import Foundation


//数据库是保存在沙盒的一个文件，需要先创建和打开数据库
//FMDB的内部队列是串行队列，同步执行。可以保证同一时间，只有一个任务操作数据库，从而保证数据库的读写安全。

class SQLiteManager
{
    //队列
    let queue: FMDatabaseQueue
    //Swift创建单例
    static let shared = SQLiteManager()
    
    private init() {
        let dbName = "category.sqlite"
        var path = NSSearchPathForDirectoriesInDomains(.documentDirectory, .userDomainMask, true)[0]
        path = (path as NSString).appendingPathComponent(dbName)
        print("数据库路径: \(path)")
        //创建数据库队列
        queue = FMDatabaseQueue(path: path)
        //创建表
        createTable()
    }
    
}

private extension SQLiteManager
{
    //创建表
    func createTable()
    {
        let createSql = "CREATE TABLE IF NOT EXISTS goods (goodsID INTEGER NOT NULL PRIMARY KEY, userID INTEGER NOT NULL, goodsInfo TEXT);"
        queue.inDatabase { (db) in
            if db.executeStatements(createSql) == true
            {
                print("创表成功")
            }
            else
            {
                print("创表失败")
            }
        }
    }
}

//插入表
extension SQLiteManager
{
    func insert(userID: String)
    {
        //"INSERT OR REPLACE INTO weibo(statusID, userID, Text) VALUES(?, ?, ?);"
        let insertSql = "INSERT OR REPLACE INTO goods(goodsID, userID, goodsInfo) VALUES(?, ?, ?);"
        //"INSERT OR REPLACE INTO goods (goodsID, userID, goodsInfo) VALUES(?, ? ,?);"
        queue.inTransaction { (db, roolBack) in
            for i in 0..<10 {
                let goodsID1 = "\(i)"
                let goodsInfo1 = "脆甜嗨翻天" + "\(i)"
                if db.executeUpdate(insertSql, withArgumentsIn: [goodsID1, userID, goodsInfo1]) == false
                {
                    //需要回滚
                    roolBack.pointee = true
                    break
                }
                else
                {
                    print("插入成功")
                }
            }
        }
    }
}

//删除
extension SQLiteManager
{
    func delete()
    {
        //删除全部数据
        let deleteSql = "DELETE FROM goods"
        //删除表
        //let deleteSql1 = "DROP TABLE IF EXISTS goods"
        queue.inDatabase { (db) in
            if db.executeUpdate(deleteSql, withArgumentsIn: [])
            {
                print("删除成功")
            }
            else
            {
                print("删除失败")
            }
        }
    }
}
//查询
extension SQLiteManager
{
    func query()
    {
        var resultArray = [String]()
        
        let selectSql = "SELECT * FROM goods;"
        queue.inDatabase { (db) in
            guard let resultSet = db.executeQuery(selectSql, withArgumentsIn: []) else
            {
                return
            }
            while resultSet.next()
            {
                let goodsID = resultSet.int(forColumn: "goodsID")
                let userID = resultSet.int(forColumn: "userID")
                guard let goodsInfo = resultSet.string(forColumn: "goodsInfo")
                else
                {
                    continue
                }
                let resultString = "\(goodsID)  \(userID)  " + goodsInfo
                resultArray.append(resultString)
                /*
                 let columnCount = resultSet.columnCount
                for i in 0..<columnCount
                {
                    guard let columnName = resultSet.columnName(for: i),
                          let columnValue = resultSet.object(forColumnIndex: i) else
                    {
                        continue
                    }
                    result.append([columnName: columnValue])
                }
 */
            }
        }
        print(resultArray)
    }
}
